﻿using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Configuration;
using System.Data.SQLite;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Web;
using Utility.Data.DbClient;
using Utility.Extensions;
using System.Text;

namespace Utility.DBUtility.DatabaseOperate
{
    /// <summary>
    /// Copyright (C) 2011 Maticsoft 
    /// 数据访问基础类(基于SQLite)
    /// </summary>
    public class DbHelperSQLite: DbHelperBase
    {
        /// <summary>
        /// 
        /// </summary>
        public DbHelperSQLite(string connectionStringName = "DefaultConnection")
                       : base(connectionStringName, DBParameterExtensions.DatabaseType.SQLite)
        { }
        #region Exists
        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="tableName">需要判断的表名</param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <returns></returns>
        public override bool ExistsTable(string tableName, string connectionString = "")
        {
            string sql = "SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME = @TABLE_NAME ";
            
            SQLiteParameter[] parameters = {
                                        new SQLiteParameter(":TABLE_NAME" ,DbType.String,50)
                                            };
            parameters[0].Value = tableName;
            return Exists(sql, connectionString, parameters);
        }
        /// <summary>
        /// 判断指定表指定栏位值是否存在
        /// </summary>
        /// <param name="values">栏位名称/值</param>
        /// <param name="tableName">表名</param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <returns></returns>
        public override bool Exists(Dictionary<string, object> values, string tableName, string connectionString = "")
        {
            if (values == null || values.Count == 0)
            {
                return false;
            }
            var keys = values.Keys;
            StringBuilder where = new StringBuilder();
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            foreach (string column in keys)
            {
                if (where.Length > 0)
                {
                    where.Append(" AND ");
                }
                where.AppendFormat(" {0}=@{0} ", column);
                parameters.Add(new SQLiteParameter(":" + column, values[column]));
            }
            string sql = string.Format(@"SELECT COUNT(1) FROM {0} WHERE {1} ", tableName, where.ToString());
            return Exists(sql, connectionString, parameters.ToArray());
        }
        /// <summary>
        /// 判断指定表指定栏位值是否存在
        /// </summary>
        /// <param name="values">栏位名称/值</param>
        /// <param name="tableName">表名</param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <returns></returns>
        public override bool Exists(List<Utility.DBUtility.CustomParameter> list, string tableName, string connectionString = "")
        {
            if (list == null || list.Count == 0)
            {
                return false;
            }
            StringBuilder where = new StringBuilder();
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            foreach (CustomParameter column in list)
            {
                if (where.Length > 0)
                {
                    where.Append(" AND ");
                }
                where.AppendFormat(" {0}=@{1} ", column.ColumnName, column.Parameter);
                parameters.Add(new SQLiteParameter(":" + column.Parameter, column.Value));
            }
            string sql = string.Format(@"SELECT COUNT(1) FROM {0} WHERE {1} ", tableName, where.ToString());
            return Exists(sql, connectionString, parameters.ToArray());
        }

        /// <summary>
        /// 删除指定表数据
        /// </summary>
        /// <param name="values"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override int Delete(List<Utility.DBUtility.CustomParameter> list, string tableName, string connectionString = "")
        {
            if (list == null || list.Count == 0)
            {
                return 0;
            }
            StringBuilder where = new StringBuilder();
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            foreach (CustomParameter column in list)
            {
                if (where.Length > 0)
                {
                    where.Append(" AND ");
                }
                where.AppendFormat(" {0}=@{1} ", column.ColumnName, column.Parameter);
                parameters.Add(new SQLiteParameter(":" + column.Parameter, column.Value));
            }
            string sql = string.Format(@"DELETE {0} WHERE {1} ", tableName, where.ToString());
            return ExecuteSql(sql, connectionString, parameters.ToArray());
        }
        /// <summary>
        /// 删除指定表数据
        /// </summary>
        /// <param name="values"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override int Delete(Dictionary<string, object> values, string tableName, string connectionString = "")
        {
            if (values == null || values.Count == 0)
            {
                return 0;
            }
            var keys = values.Keys;
            StringBuilder where = new StringBuilder();
            List<SQLiteParameter> parameters = new List<SQLiteParameter>();
            foreach (string column in keys)
            {
                if (where.Length > 0)
                {
                    where.Append(" AND ");
                }
                where.AppendFormat(" {0}=@{0} ", column);
                parameters.Add(new SQLiteParameter(":" + column, values[column]));
            }
            string sql = string.Format(@"DELETE {0} WHERE {1} ", tableName, where.ToString());
            return ExecuteSql(sql, connectionString, parameters.ToArray());
        }
        /// <summary>
        /// 判断指定表指定栏位值是否存在
        /// </summary>
        /// <param name="column">列名</param>
        /// <param name="value">值</param>
        /// <param name="tableName">表名</param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <returns></returns>
        public override bool Exists(string column, dynamic value, string tableName, string connectionString = "")
        {
            string sql = string.Format(@"SELECT COUNT(1) FROM {0} WHERE {1}=@VALUE ", tableName, column);
            SQLiteParameter[] parameters = { new SQLiteParameter(":VALUE", value) };
            return Exists(sql, connectionString, parameters);
        }
        #endregion

        #region ExecuteSql/ExecuteScalar/ExecuteReader
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="strSqlList">多条SQL语句</param>  
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>  
        public override int ExecuteSqlTran(List<string> strSqlList, string connectionString = "")
        {
            strSqlList.ForEach(t =>
            {
                t = t.ToSql(DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
            });
            if (!string.IsNullOrEmpty(base.Transactionid) || !string.IsNullOrEmpty(base.AutoTransactionid))
            {
                if (null != base.AdoNetObject || null != base.AutoAdoNetObject)
                {
                    int count = 0;
                    for (int n = 0; n < strSqlList.Count; n++)
                    {
                        string strsql = strSqlList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            count += Tran_ExecuteSql(strsql);
                        }
                    }
                    return count;
                }
                else
                {
                    base.AutoTransactionid = string.Empty;
                    base.Transactionid = string.Empty;
                }
            }
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_WriteConnectionString;
            }
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    SQLiteTransaction tx = conn.BeginTransaction();
                    cmd.Transaction = tx;
                    try
                    {
                        int count = 0;
                        for (int n = 0; n < strSqlList.Count; n++)
                        {
                            string strsql = strSqlList[n];
                            if (strsql.Trim().Length > 1)
                            {
                                cmd.CommandText = strsql;
                                count += cmd.ExecuteNonQuery();
                            }
                        }
                        tx.Commit();
                        return count;
                    }
                    catch (SQLiteException E)
                    {
                        tx.Rollback();
                        throw new Exception(E.Message);
                    }
                    finally
                    {
                        cmd.Dispose();
                        conn.Close();
                    }
                }
            }
        }
        /// <summary>
        /// 获取指定表指定栏位的记录数
        /// </summary>
        /// <param name="column"></param>
        /// <param name="value"></param>
        /// <param name="tableName"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public override int GetCountByColumn(string column, dynamic value, string tableName, string connectionString = "")
        {
            string sql = string.Format(@"SELECT COUNT(1) FROM {0} WHERE {1}=@VALUE ", tableName, column);
            SQLiteParameter[] parameters = { new SQLiteParameter(":VALUE", value) };
            return ExecuteScalar<int>(sql, string.Empty, parameters);
        }

        #region 执行带参数的SQL语句
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public override int ExecuteSql(string strSql, string connectionString = "", params IDataParameter[] cmdParms)
        {
            if (!string.IsNullOrEmpty(base.Transactionid) || !string.IsNullOrEmpty(base.AutoTransactionid))
            {
                if (null != base.AdoNetObject || null != base.AutoAdoNetObject)
                {
                    return Tran_ExecuteSql(strSql, cmdParms);
                }
                else
                {
                    base.AutoTransactionid = string.Empty;
                    base.Transactionid = string.Empty;
                }
            }
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_WriteConnectionString;
            }
            strSql = strSql.ToSql(DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, strSql, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (SQLiteException E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }
        }
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        private int Tran_ExecuteSql(string strSql, params IDataParameter[] cmdParms)
        {
            strSql = strSql.ToSql(DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                try
                {
                    if (null != base.AdoNetObject)
                    {
                        PrepareCommand(cmd, base.AdoNetObject.SQLiteConnection, base.AdoNetObject.SQLiteTransaction, strSql, cmdParms);
                    }
                    else
                    {
                        PrepareCommand(cmd, base.AdoNetObject.SQLiteConnection, base.AutoAdoNetObject.SQLiteTransaction, strSql, cmdParms);
                    }
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (SQLiteException E)
                {
                    throw new Exception(E.Message);
                }
            }
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="strSqlList">SQL语句的哈希表（key为sql语句，value是该语句的IDataParameter[]）</param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <returns></returns>
        public override int ExecuteSqlTran(List<ExcuteSqlParameters> strSqlList, string connectionString = "")
        {
            int count = 0;
            if (!string.IsNullOrEmpty(base.Transactionid) || !string.IsNullOrEmpty(base.AutoTransactionid))
            {
                if (null != base.AdoNetObject || null != base.AutoAdoNetObject)
                {
                    foreach (ExcuteSqlParameters model in strSqlList)
                    {
                        IDataParameter[] cmdParms = null;
                        if (null != model.Parameters)
                        {
                            if (model.Parameters is DBParameter[] || model.Parameters is List<DBParameter> || model.Parameters is DBParameter)
                            {
                                cmdParms = DBParameterExtensions.ToDataParameter(model.Parameters, DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
                            }
                            else
                            {
                                cmdParms = (SQLiteParameter[])model.Parameters.ToArray();
                            }
                        }
                        model.Sql = model.Sql.ToSql(DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
                        count += Tran_ExecuteSql(model.Sql, cmdParms);
                    }
                    return count;
                }
                else
                {
                    base.AutoTransactionid = string.Empty;
                    base.Transactionid = string.Empty;
                }
            }
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_WriteConnectionString;
            }
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                using (SQLiteTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        //循环                       
                        foreach (ExcuteSqlParameters model in strSqlList)
                        {
                            SQLiteCommand cmd = new SQLiteCommand();
                            IDataParameter[] cmdParms = null;
                            if (null != model.Parameters)
                            {
                                if (model.Parameters is DBParameter[] || model.Parameters is List<DBParameter> || model.Parameters is DBParameter)
                                {
                                    cmdParms = DBParameterExtensions.ToDataParameter(model.Parameters, DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
                                }
                                else
                                {
                                    cmdParms = (SQLiteParameter[])model.Parameters.ToArray();
                                }
                            }
                            model.Sql = model.Sql.ToSql(DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
                            PrepareCommand(cmd, conn, trans, model.Sql, cmdParms);
                            count += cmd.ExecuteNonQuery();
                        }
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                }
            }
            return count;
        }
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public override object ExecuteScalar(string strSql, string connectionString = "", params IDataParameter[] cmdParms)
        {
            if (!string.IsNullOrEmpty(base.Transactionid) || !string.IsNullOrEmpty(base.AutoTransactionid))
            {
                if (null != base.AdoNetObject || null != base.AutoAdoNetObject)
                {
                    return Tran_ExecuteScalar(strSql, cmdParms);
                }
                else
                {
                    base.AutoTransactionid = string.Empty;
                    base.Transactionid = string.Empty;
                }
            }
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_ReadConnectonString;// m_WriteConnectionString;
            }
            strSql = strSql.ToSql(DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, strSql, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (SQLiteException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
        }
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        private object Tran_ExecuteScalar(string strSql, params IDataParameter[] cmdParms)
        {
            strSql = strSql.ToSql(DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                try
                {
                    if (null != base.AdoNetObject)
                    {
                        PrepareCommand(cmd, base.AdoNetObject.SQLiteConnection, base.AdoNetObject.SQLiteTransaction, strSql, cmdParms);
                    }
                    else
                    {
                        PrepareCommand(cmd, base.AdoNetObject.SQLiteConnection, base.AutoAdoNetObject.SQLiteTransaction, strSql, cmdParms);
                    }
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (SQLiteException e)
                {
                    throw new Exception(e.Message);
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回SQLiteDataReader ( 注意：调用该方法后，一定要对IDataReader进行Close )
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public override IDataReader ExecuteReader(string strSql, string connectionString = "", params IDataParameter[] cmdParms)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_ReadConnectonString;// m_WriteConnectionString;
            }
            strSql = strSql.ToSql(DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
            SQLiteConnection connection = new SQLiteConnection(connectionString);
            SQLiteCommand cmd = new SQLiteCommand();
            try
            {
                PrepareCommand(cmd, connection, null, strSql, cmdParms);
                SQLiteDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (SQLiteException e)
            {
                throw new Exception(e.Message);
            }
        }
        #endregion

        #endregion

        #region Query
        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public override DataTable Query(string strSql, string connectionString = "", params IDataParameter[] cmdParms)
        {
            if (!string.IsNullOrEmpty(base.Transactionid) || !string.IsNullOrEmpty(base.AutoTransactionid))
            {
                if (null != base.AdoNetObject || null != base.AutoAdoNetObject)
                {
                    return Tran_Query(strSql, cmdParms);
                }
                else
                {
                    base.AutoTransactionid = string.Empty;
                    base.Transactionid = string.Empty;
                }
            }
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_ReadConnectonString;// m_WriteConnectionString;
            }
            strSql = strSql.ToSql(DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    PrepareCommand(cmd, connection, null, strSql, cmdParms);
                    using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        try
                        {
                            da.Fill(dt);
                            cmd.Parameters.Clear();
                        }
                        catch (SQLiteException ex)
                        {
                            throw new Exception(ex.Message);
                        }
                        return dt;
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回DataTable[使用事务]
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        private DataTable Tran_Query(string strSql, params IDataParameter[] cmdParms)
        {
            strSql = strSql.ToSql(DBParameterExtensions.DatabaseType.SQLite, m_DataParamterPrefixChar);
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                if (null != base.AdoNetObject)
                {
                    PrepareCommand(cmd, base.AdoNetObject.SQLiteConnection, base.AdoNetObject.SQLiteTransaction, strSql, cmdParms);
                }
                else
                {
                    PrepareCommand(cmd, base.AdoNetObject.SQLiteConnection, base.AutoAdoNetObject.SQLiteTransaction, strSql, cmdParms);
                }
                using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    try
                    {
                        da.Fill(dt);
                        cmd.Parameters.Clear();
                    }
                    catch (SQLiteException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return dt;
                }
            }
        }
        #region 执行分页查询

        #region 带前缀分页返回DataTable
        /// <summary>
        /// 分页查询
        /// Paging query
        /// </summary>
        /// <param name="prefixSql">前缀SQL</param>
        /// <param name="strSql">
        /// 需要执行分页的SQL
        /// [如果Sql中带排序字段,则排序字段必须出现再Select的字段中,并且程序会自动将Sql中的排序作为查询的排序]
        /// </param>        
        /// <param name="totalCount">总数据行(total Count)</param>
        /// <param name="pageIndex">第几页(page Index)</param>
        /// <param name="pageSize">每页的数据行数(page Size)</param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <param name="cmdParms">查询参数(cmdParms)</param>
        /// <returns>Data Table</returns>
        public override DataTable Query(string prefixSql, string strSql, out int totalCount, string connectionString, int pageIndex = 1, int pageSize = 20, params IDataParameter[] cmdParms)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_ReadConnectonString;// m_WriteConnectionString;
            }
            if (pageIndex < 1)
            {
                pageIndex = 1;
            }
            if (pageSize < 1)
            {
                pageSize = 10;
            }
            int rowBeginIndex = (pageIndex - 1) * pageSize + 1;
            int rowEndIndex = pageIndex * pageSize;
            string myStrSql = strSql;

            totalCount = 0;
            string countSql = string.Format(@"{0} 
                                              SELECT COUNT(1) FROM ({1}) sql_tmpTable_1 ", prefixSql, myStrSql);
            string pageSql = string.Format(@"  SELECT * FROM 
                                                ({0}) AS _tmpTable_1 LIMIT {1},{2} ", strSql, rowBeginIndex, pageSize);
            var result = ExecuteScalar(countSql, cmdParms);
            totalCount = Convert.ToInt32(result);
            DataTable dt = Query(pageSql, cmdParms);
            return dt;
        }
        #endregion

        #endregion

        #endregion

        #region 存储过程操作
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public override DataSet RunProcedure(string storedProcName, string tableName, string connectionString = "", params IDataParameter[] parameters)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_WriteConnectionString;
            }
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SQLiteDataAdapter sqlDA = new SQLiteDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }
        /// <summary>
        /// 执行存储过程，返回影响的行数    
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <returns></returns>
        public override int RunProcedure(string storedProcName, out int rowsAffected, string connectionString = "", params IDataParameter[] parameters)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_WriteConnectionString;
            }
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                //int result;
                connection.Open();
                SQLiteCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                //result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();             
                return rowsAffected;
            }
        }
        #endregion

        #region OracleBulkCopy
        /// <summary>
        /// 批量导入数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <param name="disposeTable">是否销毁表,如果为false,则自动清空表数据</param>
        /// <returns></returns>
        public override bool BulkCopy(DataTable dt, string tableName, string connectionString, bool disposeTable = true)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_WriteConnectionString;
            }
            int count = DataAdapter(dt, tableName, DataRowState.Added, connectionString);
            if (disposeTable)
            {
                dt.Dispose();
            }
            else
            {
                dt.Clear();
            }
            return count > 0;
        }

        /// <summary>
        /// 批量导入数据
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="tableName"></param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <returns></returns>
        public override bool BulkCopy(IDataReader reader, string tableName, string connectionString = "")
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_WriteConnectionString;
            }
            DataTable dt = new DataTable();
            dt.Load(reader);
            return BulkCopy(dt, tableName, connectionString);
        }
        /// <summary>
        /// 批量导入数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="rowState">需要写入到DB的Row状态</param>
        /// <param name="tableName"></param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <param name="disposeTable">是否销毁表</param>
        /// <returns></returns>
        public override bool BulkCopy(DataTable dt, DataRowState rowState, string tableName, string connectionString, bool disposeTable = true)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_WriteConnectionString;
            }
            int count = DataAdapter(dt, tableName, rowState, connectionString);
            if (disposeTable)
            {
                dt.Dispose();
            }
            else
            {
                dt.Clear();
            }
            return count > 0;
        }
        /// <summary>
        /// 使用DataAdapter进行批量提交Insert,Update,Delete
        /// 如果使用Delete, DataTable中Row需要使用 Delete进行删除,不能使用Remove|RemoveAt进行移除,因为Delete只是注释数据行的状态
        /// 使用DataAdapter必须要求有主键
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <param name="unchangedDefaultState">
        /// 表结构中, Unchanged状态的Row 默认设置为哪种状态
        /// </param>
        /// <param name="connectionString">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <returns></returns>
        public override int DataAdapter(DataTable dt, string tableName, DataRowState unchangedDefaultState = DataRowState.Modified, string connectionString = "")
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_WriteConnectionString;
            }
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                string selectStrSql = string.Format("select * from {0} where 1<>1 ", tableName);
                using (SQLiteCommand cmd = new SQLiteCommand(selectStrSql, connection))
                {
                    try
                    {
                        connection.Open();
                        SQLiteDataAdapter myDataAdapter = new SQLiteDataAdapter();
                        myDataAdapter.SelectCommand = new SQLiteCommand(selectStrSql, connection);
                        SQLiteCommandBuilder custCB = new SQLiteCommandBuilder(myDataAdapter);
                        custCB.ConflictOption = ConflictOption.OverwriteChanges;
                        custCB.SetAllValues = true;
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (dr.RowState == DataRowState.Unchanged)
                            {
                                switch (unchangedDefaultState)
                                {
                                    case DataRowState.Added:
                                        dr.SetAdded();
                                        break;
                                    case DataRowState.Deleted:
                                        dr.Delete();
                                        break;
                                    case DataRowState.Modified:
                                    default:
                                        dr.SetModified();
                                        break;
                                }
                            }
                        }
                        int count = myDataAdapter.Update(dt);
                        dt.AcceptChanges();
                        myDataAdapter.Dispose();
                        return count;
                    }
                    catch (Exception ex)
                    {
                        connection.Close();
                        throw ex;
                    }
                }
            }
        }
        #endregion
        /// <summary>
        /// 参数构建
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, IDataParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null && cmdParms.Length > 0)
            {
                foreach (IDataParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }
            }
        }
        /// <summary>
        /// 创建 SQLiteCommand 对象实例(用来返回一个整数值)  
        /// </summary>
        /// <param name="connection">数据库链接字符串(为空则使用默认连接字符串)</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SQLiteCommand 对象实例</returns>
        private SQLiteCommand BuildIntCommand(SQLiteConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SQLiteCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            /*
            command.Parameters.Add(new SQLiteParameter(":ReturnValue",
                                                        OracleDbType.Int32, 4, ParameterDirection.ReturnValue,
                                                        false, 0, 0, string.Empty, DataRowVersion.Default, null));
            */
            return command;
        }
        /// <summary>
        /// 构建 SQLiteCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SQLiteCommand</returns>
        private SQLiteCommand BuildQueryCommand(SQLiteConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SQLiteCommand command = new SQLiteCommand();
            command.Connection = connection;
            command.CommandText = storedProcName;
            command.CommandType = CommandType.StoredProcedure;
            //command.BindByName = true;
            foreach (IDataParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }
        #region ORM 事务对象
        /// <summary>
        /// 当前项目中使用,自动提交事务
        /// </summary>
        /// <returns></returns>
        internal override string AutoBeginTransaction(string connectionString = "", IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_WriteConnectionString;
            }
            if (!string.IsNullOrEmpty(base.Transactionid) && ORMCommData.AdoNetObjects.ContainsKey(base.Transactionid))
            {   //如果存在事务ID, 要先判断该事务ID是否还存在,如果存在,则将事务ID返回,防止重复开启事务
                if (!string.IsNullOrEmpty(base.AutoTransactionid))
                {
                    ORMCommData.AdoNetObjects.TryRemove(AutoTransactionid, out _AutoAdoNetObject);
                    _AutoAdoNetObject = null;
                    base.AutoTransactionid = string.Empty;//如果存在外围的事务ID,则清除内部自动事务
                }
                return base.Transactionid;
            }
            else
            {
                base.Transactionid = string.Empty;
            }
            if (!string.IsNullOrEmpty(base.AutoTransactionid) && ORMCommData.AdoNetObjects.ContainsKey(base.AutoTransactionid))
            {
                return base.AutoTransactionid;
            }
            AdoNetObject adoObject = new AdoNetObject()
            {
                SQLiteConnection = new SQLiteConnection(connectionString)
            };
            adoObject.SQLiteConnection.Open();
            adoObject.SQLiteTransaction = adoObject.SQLiteConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            string transactionid = Guid.NewGuid().ToString();
            ORMCommData.AdoNetObjects.TryAdd(transactionid, adoObject);
            base.AutoTransactionid = transactionid;
            return transactionid;
        }
        /// <summary>
        /// 开启事务
        /// [目前不支持存储过程放入事务中]
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="isolationLevel">事务开启级别</param>
        public override string BeginTransaction(string connectionString = "", IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                connectionString = m_WriteConnectionString;
            }
            if (!string.IsNullOrEmpty(base.Transactionid) && ORMCommData.AdoNetObjects.ContainsKey(base.Transactionid))
            {   //如果存在事务ID, 要先判断该事务ID是否还存在,如果存在,则将事务ID返回,防止重复开启事务
                if (!string.IsNullOrEmpty(base.AutoTransactionid))
                {
                    ORMCommData.AdoNetObjects.TryRemove(AutoTransactionid, out _AutoAdoNetObject);
                    _AutoAdoNetObject = null;
                    base.AutoTransactionid = string.Empty;//如果存在外围的事务ID,则清除内部自动事务
                }
                return base.Transactionid;
            }
            AdoNetObject adoObject = new AdoNetObject()
            {
                SQLiteConnection = new SQLiteConnection(connectionString)
            };
            adoObject.SQLiteConnection.Open();
            adoObject.SQLiteTransaction = adoObject.SQLiteConnection.BeginTransaction(isolationLevel);
            string transactionid = Guid.NewGuid().ToString();
            ORMCommData.AdoNetObjects.TryAdd(transactionid, adoObject);
            base.Transactionid = transactionid;
            return transactionid;
        }

        #endregion
    }
}
